4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \par \pard\plain \s31\sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1080\tx1440\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \f122\fs18 \tab put "UPPER(FIRSTNAME) = 'DAVID'" into queryString\par \pard \s31\sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1080\tx1440\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \tab put DBQuery(queryString) into searchResult\par \pard\plain \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \f8 \par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 Notice that the query string, like most normal strings, is bounded by double quotes and placed into the variable queryString. Again, the host environment only knows this as a string and does not parse the contents of the string.\par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 But what if you wanted to replace the literal 'DAVID' with a string of your own choosing? Let's assume that you wanted to find the record matching the contents of the variable myFirstName. What many people do, and what WILL NOT WORK is the following:\par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \par \pard\plain \s31\sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1080\tx1440\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \f122\fs18 \tab -- the following won't work\par \pard \s31\sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1080\tx1440\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \tab put "UPPER(FIRSTNAME) = 'myFirstName'" into queryString\par \tab put DBQuery(queryString) into searchResult\par \pard\plain \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \f8 \par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 All the above does is ask DBQuery to find a record where the contents of the field FIRSTNAME contains the literal string 'myFirstName'. What you need to do is construct a complex string. This is just pure Lingo or HyperTalk, there's no FileFlex magic here. The easiest way to see this is to construct a string in the following way:\par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \par \pard\plain \s31\sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1080\tx1440\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \f122\fs18 \tab -- store the first half of the query expression string\par \pard \s31\sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1080\tx1440\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \tab put "UPPER(FIRSTNAME) = '" into firstHalf\par \tab -- build the string properly\par \tab put firstHalf & myFirstName & "'" into queryString\par \tab put DBQuery(queryString) into searchResult\par \pard\plain \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \f8 \par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 Take extra care to notice that the single quote (') is contained in the double quotes and is passed to DBQuery. By making sure that the myFirstName variable is passed outside the double quotes, you're ensuring that it's evaluated by your host environment before it's passed to DBQuery.\par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 Confusion evaluating strings has got to be one of the most common technical support calls. So read and study the descriptions above, learn about strings in your host language, and please make sure you understand how strings work in your host language before calling us up. We'll just tell you to read this section anyway! \par \pard\plain \s2\sb480\keepn\widctlpar \b\f8\fs36 {\*\bkmkstart _Toc349722613}{\*\bkmkstart _Toc349722837}{\*\bkmkstart _Toc349723126}{\*\bkmkstart _Toc349723273}Clearing a Search Condition{\*\bkmkend _Toc349722613}{\*\bkmkend _Toc349722837}{\*\bkmkend _Toc349723126}{\*\bkmkend _Toc349723273}\par \pard\plain \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \f8 \par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 Once you have set up a search condition with DBLocate, it stays in effect until you invoke another one. DBSkip will follow the database sequence looking for records that match the criterion in the last DBLocate command. This chain is broken by use of the command:\par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \par \pard\plain \s31\sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1080\tx1440\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \f122\fs18 \tab DBLocate("")\par \pard\plain \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \f8 \par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 Thereafter, DBSkip will revert to its previous mode of operation (sequential or sort-order movement through the file). \par \pard\plain \s1\sb480\keep\keepn\pagebb\widctlpar \b\f8\fs72 {\*\bkmkstart _Toc349722614}{\*\bkmkstart _Toc349722838}{\*\bkmkstart _Toc349723127}{\*\bkmkstart _Toc349723274}14. Ultra-fast Searching with Indexes{\*\bkmkend _Toc349722614}{\*\bkmkend _Toc349722838}{\*\bkmkend _Toc349723127}{\*\bkmkend _Toc349723274}\par \pard\plain \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \f8 \par \pard\plain \s19\fi-360\li720\widctlpar \f8 \bullet \tab Index Files Supported \par \bullet \tab When Indexes are Updated Automatically \par \bullet \tab Opening and Using Index Files \par \bullet \tab Finding a Record by Index \par \bullet \tab Building a Seek Expression \par \bullet \tab Getting Index File Information \par \bullet \tab Creating New Indexes and Updating Old Ones \par \bullet \tab Multi-Field Indexes \par \bullet \tab Intrinsic Functions in Indexes \par \bullet \tab You Must Index on a String Data Type \par \pard\plain \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \f8 \par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 FileFlex excels at index files. Letting FileFlex use indexes is like giving a prized racehorse the chance to just let it all out, throttling up a perfectly tuned race car, or punching a fighter jet into afterburner. When we say that FileFlex can locate any record in a sea of billions of records faster than the blink of an eye, we're talking FileFlex indexes.\par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 Whenever possible, we recommend you use indexed-based searching. Indexes work by algorithm, rather than brute force. FileFlex looks at the string you're search for, does a mathematical calculation that basically tells it how far into the file to move, and boom, it's on the record you need. By contrast, both DBQuery and the full-text search DBFindMemo scan on a record by record basis. This means that if the data you're looking for is at the end of the file, DBQuery and DBFindMemo must individually check all the preceding records prior to finding the match. DBSeek (the interface to indexes) just does a calculation and whammo! You're on the record.\par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 Indexes are not without their price, however. Since indexes can't check every record and rely instead on complex offset calculations, they don't support complex queries. But they're ideal for most queries you'll need. Need to find someone's address? Construct an index combining last name and first name, do a DBSeek, and--poof!--you're on the record. Need to find everyone in the Southeast Region who's booked over $1.25 million and who hasn't gotten a recent raise? Use DBQuery and be prepared to wait a while.\par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 Indexes also take disk space, often quite a lot. Each index is it's own file and that file contains the complete data of the field it's indexing, as well as some overhead space used internally by FileFlex. So, if you've got this handy name and address database and you want to index on last name, followed by first name, you've got one index file. If you want to index based on zipcode, you've got another index file, and so forth. But what the heck. CD-ROMs are big, text is small, and new hard drives are cheap. Use indexes and rejoice in the raw speed!\par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 {\b Note:} It is possible to use DBSeek and DBQuery incorrectly and get dog-poor performance. These tools provide you with the capabilities. But it's up to you to design something that works efficiently. Just because you've got a hot database engine is no excuse for poor application design. \par \pard\plain \s2\sb480\keepn\widctlpar \b\f8\fs36 {\*\bkmkstart _Toc349722615}{\*\bkmkstart _Toc349722839}{\*\bkmkstart _Toc349723128}{\*\bkmkstart _Toc349723275}Index Files Supported{\*\bkmkend _Toc349722615}{\*\bkmkend _Toc349722839}{\*\bkmkend _Toc349723128}{\*\bkmkend _Toc349723275}\par \pard\plain \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \f8 \par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 FileFlex supports the use and updating of dBASE III-compatible index files only. Other index file architectures such as FoxPro indexes cannot be used. However, FileFlex can reindex a file using the dBASE index file structures. This makes it easy to use files which have been indexed using other methods. \par \pard\plain \s3\sb360\keepn\widctlpar \b\f8\fs28 {\*\bkmkstart _Toc349722616}When Indexes are Updated Automatically{\*\bkmkend _Toc349722616}\par \pard\plain \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \f8 \par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 Any time you have opened one or more index files related to an open database file and you make changes to that database file, FileFlex automatically updates those indexes to reflect the new file contents. \par \pard\plain \s2\sb480\keepn\widctlpar \b\f8\fs36 {\*\bkmkstart _Toc349722617}{\*\bkmkstart _Toc349722840}{\*\bkmkstart _Toc349723129}{\*\bkmkstart _Toc349723276}Opening and Using Index Files{\*\bkmkend _Toc349722617}{\*\bkmkend _Toc349722840}{\*\bkmkend _Toc349723129}{\*\bkmkend _Toc349723276}\par \pard\plain \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \f8 \par Among the functions you may wish to perform on an index file are the following: \par \par \pard\plain \s19\fi-360\li720\widctlpar \f8 \bullet \tab open a specific index file \par \bullet \tab check an open index file to be sure it matches the file's contents \par \bullet \tab select from among two or more open index files to make one current \par \bullet \tab close an open index file \par \pard\plain \s3\sb360\keepn\widctlpar \b\f8\fs28 {\*\bkmkstart _Toc349722618}Opening an Index File{\*\bkmkend _Toc349722618}\par \pard\plain \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \f8 \par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 Use the FileFlex DBUseIndex function to open an index file for use. Supply the index file's name as an argument. Assign the result of this function to a variable (usually global) because you'll need to refer to the index file's ID in other scripts and handlers. Here's an example of the use of this function:\par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \par \pard\plain \s31\sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1080\tx1440\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \f122\fs18 \tab put DBUseIndex("STARS") into StarIndex\par \pard\plain \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \f8 \par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 Note that the name of the index file should include any extension the file might have. In the Macintosh environment, extensions are normally omitted, but in DOS environments, the file extension ".NDX" will generally be used for dBASE III-compatible index files. \par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 We regularly get tech support calls from customers who get index file errors. The most common reason is that the database the index file indexes must be open and selected (DBUse and DBSelect) before executing a DBUse. Oex indexID DBCheckActive return FileFlex("34", string(indexID))end DBCheckIndexon DBQuery expr DBCheckActive return FileFlex("35",expr)end DBQueryon DBLocate expr -- NOTE: DBLocate is obsolete and will be eliminated in future releases -- DBLocate has been replaced by DBQuery DBCheckActive return FileFlex("35",expr)end DBLocateon DBCurrDBNum DBCheckActive return FileFlex("36")end DBCurrDBNumon DBEncrypt theString, key DBCheckActive return FileFlex("38",theString, key)end DBEncrypton DBDecrypt theString, key DBCheckActive return FileFlex( "39", theString, key)end DBDecrypton DBFindMemo memoField, theString DBCheckActive return FileFlex("40",memoField, theString)end DBFindMemoon DBConvertCRLF theString, theOption DBCheckActive if the paramCount = 1 then return FileFlex("41",theString) else return FileFlex("41",theString, theOption) end ifend DBConvertCRLFon DBPlatform DBCheckActive return FileFlex("42")end DBPlatformon DBTranslateChars string, table DBCheckActive return FileFlex("48",string, table)end DBTranslateCharson DBSetSortOrder order global gDBWorldSort global gDBSortOrder if order = EMPTY then put EMPTY into gDBWorldSort else put "1" into gDBWorldSort put order into gDBSortOrder end if return 0end DBSetSortOrderon DBSetCaseTables upperTable, lowerTable global gDBWorldCase global gDBWorldUpper, gDBWorldLower if (upperTable = EMPTY or lowerTable = EMPTY) then put EMPTY into gDBWorldCase else put "1" into gDBWorldCase put upperTable into gDBWorldUpper put lowerTable into gDBWorldLower end if return 0end DBSetCaseTableson DBUpper s DBCheckActive return FileFlex("49",string(s))end DBUpperon DBLower s DBCheckActive return FileFlex("50",string(s))end DBLoweron DBCheckActive global gDBActive1030 if gDBActive1030 <> "true" then put DBOpenSession() into dummy end ifend DBCheckActive-- These routines should never be called by the user. They simply-- assign and gather values of the global variables. They are here so-- we can test the global value interface to the host application--on DBSetGlobal globName, globVal return FileFlex("202",string(globName),string(globVal))end DBSetGlobalon DBGetGlobal globName return FileFlex("201",string(globName))end DBGetGlobalFF Wrappers Y |‡è û û G0